package com.example.examsystem.repository;

import com.example.examsystem.model.ExamRecord;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;
import java.util.Optional;

/**
 * 考试记录数据访问层
 * 提供考试记录相关的数据库操作方法
 */
@Repository
public interface ExamRecordRepository extends JpaRepository<ExamRecord, Long> {
    
    /**
     * 根据用户ID查询考试记录
     * @param userId 用户ID
     * @return 考试记录列表
     */
    List<ExamRecord> findByUserId(Long userId);
    
    /**
     * 根据考试ID查询考试记录
     * @param examId 考试ID
     * @return 考试记录列表
     */
    List<ExamRecord> findByExamId(Long examId);
    
    /**
     * 根据用户ID和考试ID查询考试记录
     * @param userId 用户ID
     * @param examId 考试ID
     * @return 考试记录列表
     */
    List<ExamRecord> findByUserIdAndExamId(Long userId, Long examId);
    
    /**
     * 根据用户ID和考试ID查询最新的考试记录
     * @param userId 用户ID
     * @param examId 考试ID
     * @return 最新的考试记录
     */
    Optional<ExamRecord> findFirstByUserIdAndExamIdOrderByCreatedAtDesc(Long userId, Long examId);
    
    /**
     * 根据状态查询考试记录
     * @param status 状态
     * @return 考试记录列表
     */
    List<ExamRecord> findByStatus(String status);
    
    /**
     * 查询用户在指定考试中的记录数量
     * @param userId 用户ID
     * @param examId 考试ID
     * @return 记录数量
     */
    long countByUserIdAndExamId(Long userId, Long examId);
    
    /**
     * 分页查询考试记录（支持多条件筛选）
     * @param examId 考试ID
     * @param userId 用户ID
     * @param username 用户名
     * @param examTitle 考试标题
     * @param status 状态
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param pageable 分页参数
     * @return 分页考试记录数据
     */
    @Query("""
       SELECT er FROM ExamRecord er
       LEFT JOIN er.exam e
       LEFT JOIN er.user u
       WHERE
           (:examId IS NULL OR er.examId = :examId) AND
           (:userId IS NULL OR er.userId = :userId) AND
           (:username IS NULL OR :username = '' OR LOWER(u.username) LIKE LOWER(CONCAT('%', :username, '%'))) AND
           (:examTitle IS NULL OR :examTitle = '' OR LOWER(e.title) LIKE LOWER(CONCAT('%', :examTitle, '%'))) AND
           (:status IS NULL OR :status = '' OR er.status = :status) AND
           (:startTime IS NULL OR er.startTime >= :startTime) AND
           (:endTime IS NULL OR er.startTime <= :endTime)
       """)
    Page<ExamRecord> findByQuery(@Param("examId") Long examId,
                                @Param("userId") Long userId,
                                @Param("username") String username,
                                @Param("examTitle") String examTitle,
                                @Param("status") String status,
                                @Param("startTime") LocalDateTime startTime,
                                @Param("endTime") LocalDateTime endTime,
                                Pageable pageable);
    
    /**
     * 查询用户的考试记录（分页）
     * @param userId 用户ID
     * @param examId 考试ID（可选）
     * @param status 状态（可选）
     * @param pageable 分页参数
     * @return 分页考试记录数据
     */
    @Query("""
       SELECT er FROM ExamRecord er
       LEFT JOIN er.exam e
       WHERE
           er.userId = :userId AND
           (:examId IS NULL OR er.examId = :examId) AND
           (:status IS NULL OR :status = '' OR er.status = :status)
       """)
    Page<ExamRecord> findMyRecords(@Param("userId") Long userId,
                                  @Param("examId") Long examId,
                                  @Param("status") String status,
                                  Pageable pageable);
    
    /**
     * 查询考试统计信息
     * @param examId 考试ID
     * @return 统计信息
     */
    @Query(value = """
       SELECT 
           COUNT(*) as totalCount,
           COUNT(CASE WHEN status = 'COMPLETED' THEN 1 END) as completedCount,
           COUNT(CASE WHEN status = 'IN_PROGRESS' THEN 1 END) as inProgressCount,
           AVG(CASE WHEN score IS NOT NULL THEN score END) as avgScore,
           MAX(CASE WHEN score IS NOT NULL THEN score END) as maxScore,
           MIN(CASE WHEN score IS NOT NULL THEN score END) as minScore 
       FROM exam_records
       WHERE exam_id = :examId
       """, nativeQuery = true)
    Map<String, Object> getExamStatistics(@Param("examId") Long examId);
    
    /**
     * 统计考试中及格的人数
     * @param examId 考试ID
     * @param score 分数
     * @return 及格人数
     */
    long countByExamIdAndScoreGreaterThanEqual(@Param("examId") Long examId, @Param("score") BigDecimal score);
    
    /**
     * 查询考试中完成状态记录的分数
     * @param examId 考试ID
     * @param status 状态
     * @return 分数列表
     */
    @Query("SELECT er.score FROM ExamRecord er WHERE er.examId = :examId AND er.status = :status AND er.score IS NOT NULL")
    List<BigDecimal> findScoresByExamIdAndStatus(@Param("examId") Long examId, @Param("status") String status);
}